﻿-- ============================================= 
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_Create_G20150114] 
	(
		@StartDate NCHAR(10),
		@EndDate NCHAR(10),
		@userid VARCHAR(10),
		@username NVARCHAR(10)
	)
AS
BEGIN
	
---------------------------------------------------------------------------------------------
DECLARE @bd int,@wz int,@qt INT,@qyQQ INT,@QYQQ_CustomerCount INT,@qytz INT,@idc INT,@gdzc int,@xfml DECIMAL(18,2),@xsml DECIMAL(18,2),@xse DECIMAL(18,2),@albb DECIMAL(18,2)
DECLARE @mlhj DECIMAL(18,2),@xzys DECIMAL(18,2),@wksr DECIMAL(18,2),@tx DECIMAL(18,2),@rcfy DECIMAL(18,2),@cl DECIMAL(18,2)
DECLARE @sscl DECIMAL(18,2),@zxf DECIMAL(18,2)
DECLARE @bd_je DECIMAL(18,2),@wz_je DECIMAL(18,2),@qyQQ_je  DECIMAL(18,2),@qt_je  DECIMAL(18,2),@idc_je DECIMAL(18,2)
 declare @xzxse DECIMAL(18,2),@xfxse DECIMAL(18,2),@bdztc int,@bdztc_je DECIMAL(18,2),@ygbx DECIMAL(18,2),@jj DECIMAL(18,2),@bcfk DECIMAL(18,2),@qqqdfqzcml DECIMAL(18,2),@qqqdfqzcxse DECIMAL(18,2)
 declare @yggz DECIMAL(18,2)	,@sf decimal(18,2),@ff decimal(18,2),@zbfy decimal(18,2),@ygfl decimal(18,2),@sjglf decimal(18,2),@qtzc  decimal(18,2),@fyze   decimal(18,2)
declare @jkzc decimal(18,2),@jksr decimal(18,2),@tzzc decimal(18,2),@tzsr decimal(18,2),@dkzc decimal(18,2),@dksr decimal(18,2),@ssclwsr decimal(18,2),@fyyxsr decimal(18,2),@fh decimal(18,2),@ssclwzc decimal(18,2)

---------------------------------------------------------------------------------------------

Declare @indextable Table(id int Identity(1,1),cid int,cname nvarchar(100),nid int,maname nvarchar(50))
Insert Into @indextable(cid,cname,nid,maname)  


----2014.12.3 管理区下设二级管理区，查询管理区ID和名称时分开查找
 Select companyid ,name ,
  Pid,
 isnull((SELECT ma.name FROM	zzjg ma WHERE ma.Id=zzjg.pid AND ma.companyid=0 and (ma.[level]=2 or ma.[level]=3)),'') as a
 
From zzjg WHERE  companyid <>0 and [level]=3


	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	
	--与当前月份相比，相隔两个月以上的时间段不能再生成月报
	--只能生成当前月，以及上一个月的月报
	if (DATEDIFF(month,@sRq,getdate()) >1	)
	return 

/*******************************/
DELETE [FinanceMonthTable] WHERE  left(Convert(nchar(10),TMonth,120),7)=left(Convert(nchar(10),@StartDate,120),7) 
;with list as (
select 
			 CompanyId          
		   ,isnull(sum([bd]),0) as bd
           ,isnull(sum([wz]),0) as wz
           ,isnull(sum([qt]),0) as qt
           ,isnull(sum([IAB]),0) as xfml 
           ,isnull(sum([IAA]),0) as xsml
           ,isnull(sum(IAE),0) as xse
           ,isnull(sum([IAA]+[IAB]),0) as mlhj
           ,isnull(sum([IAC]),0) as xzys
           ,isnull(sum([IAF]),0) as wksr         
           ,isnull(sum([rcfy]),0) as rcfy
           ,isnull(sum([cl]),0) as cl
           ,isnull(sum([sscl]),0) as sscl
           ,isnull(sum([zxf]),0) as zxf                     
           ,isnull(sum(qyQQ),0) as qyQQ
		   ,ISNULL(SUM(QYQQ_CustomerCount),0) AS QYQQ_CustomerCount       
           ,isnull(sum(gdzc),0) as gdzc,         
           isnull(sum(jkzc),0) as jkzc,
           isnull(sum(jksr),0) as jksr,
           isnull(sum(dkzc),0) as dkzc,
           isnull(sum(dksr),0) as dksr,
           isnull(sum(tzzc),0) as tzzc ,
           isnull(sum(tzsr),0) as tzsr,
           isnull(sum(yggz),0) as yggz,
		   isnull(sum(OEB),0) as ygbx,
           isnull(sum(sf),0) as sf,
           isnull(sum(ff),0) as ff,
           isnull(sum(zbfy),0) as zbfy,
           isnull(sum(ygfl),0) as ygfl,
           isnull(sum(sjglf),0) as sjglf,
           isnull(sum(qtzc),0) as qtzc,
           isnull(sum(fyze),0) as fyze,
           isnull(sum(fh),0) as fh,
           isnull(sum(ssclwsr),0) as ssclwsr,
           isnull(sum(fyyxsr),0) as fyyxsr
           ,isnull(sum(xfxse),0) as xfxse,
           isnull(sum(xzxse),0) as xzxse,
           isnull(sum(bd_je),0) as bd_je,
           isnull(sum(wz_je),0) as wz_je,
           isnull(sum(qyQQ_je),0) as qyQQ_je,
           isnull(sum(ssclwzc),0) as ssclwzc,
           isnull(sum(qt_je),0) as qt_je,
           isnull(sum(idc),0) as idc,
           isnull(sum(idc_je),0) as idc_je,
           isnull(sum(jj),0) as jj,
		   isnull(sum(bcfk),0) as bcfk,
		   isnull(sum(kqfk),0) as kqfk,
		   isnull(sum(qqqdfqzcml),0) as qqqdfqzcml,
		   isnull(sum(qqqdfqzcxse),0) as qqqdfqzcxse,
		   isnull(sum(OEE),0) as dhkdf,
		   isnull(sum(OEF),0) as sdf,
		   isnull(sum(OEG),0) as bgfy,
		   isnull(sum(OEI),0) as clf,
		   isnull(sum(OEJ),0) as zdf,
		   isnull(sum(OEK),0) as cwfy,
		   isnull(sum(OEL),0) as zppxf,
		   isnull(sum(OEN),0) as scxcf,
		   isnull(sum(BDZTC),0) as bdztc,
		   isnull(sum(BDZTCXSE),0) as bdztcxse
from Statistics_Financial where createdate between @sRq and @eRq group by companyid
),
list1 as (
	select i.*,list.* from @indextable as i ,list where i.cid=list.companyid
)

INSERT INTO [FinanceMonthTable]
([MAid]
,[MAname]
,[bd]
,[wz]
,[qt]
,[xfml]
,[xsml]
,[xse]
,[mlhj]
,[xzys]
,[wksr]
,[tx]
,[rcfy]
,[cl]
,[sscl]
,[zxf]
,[createdate]
,[userid]
,[username]
,TMonth
,qyQQ
,QYQQ_CustomerCount
,tz
,CompanyId
,CompanyName
,gdzc,albb,jkzc,jksr,dkzc,dksr,tzzc,tzsr,yggz,ygbx,sf,ff,zbfy,ygfl,sjglf,qtzc,fyze,fh,ssclwsr,fyyxsr
,xfxse,xzxse,bd_je,wz_je,qyQQ_je,ssclwzc,qt_je,idc,idc_je,jj,bcfk,kqfk,qqqdfqzcml,qqqdfqzcxse,bdztc,bdztcxse,dhkdf,sdf,bgfy,clf,zdf,cwfy,zppxf,scxcf)
select 
nid
,maname

,[bd]
,[wz]
,[qt]
,[xfml]
,[xsml]
,[xse]
,[mlhj]
,[xzys]
,[wksr]
,0
,[rcfy]
,[cl]
,[sscl]
,[zxf]
,getdate()
,@userid
,@username
,@StartDate
,qyQQ
,QYQQ_CustomerCount
,0
,cid
,cname
,gdzc,0,jkzc,jksr,dkzc,dksr,tzzc,tzsr,yggz,ygbx,sf,ff,zbfy,ygfl,sjglf,qtzc,fyze,fh,ssclwsr,fyyxsr
,xfxse,xzxse,bd_je,wz_je,qyQQ_je,ssclwzc,qt_je,idc,idc_je,jj,bcfk,kqfk,qqqdfqzcml,qqqdfqzcxse,bdztc,bdztcxse,dhkdf,sdf,bgfy,clf,zdf,cwfy,zppxf,scxcf from list1 

	
	
	
	
		
	
END
RETURN